# -*- coding: utf-8 -*-
# author:HHR
# time:2021/1/14:18:10
# ___msg___ = 'XH年费'
import sys

from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait
from quote_folder.Db_connect import DB_connect
from selenium.webdriver.common.by import By
from selenium import webdriver
from bs4 import BeautifulSoup
import time, random, re
from lxml import etree


class annual_msg():
    def __init__(self):
        self.get_url = "http://cpquery.cnipa.gov.cn/"
        self.develop_con = DB_connect().online_db()  # 开发数据库连接
        self.develop_cur = self.develop_con.cursor()
        self.bigdata_con = DB_connect().ccy_db()  # 数据组数据库连接
        self.bigdata_cur = self.bigdata_con.cursor()
        self.redis_con = DB_connect().patents_reids()  # redis连接

    def create_table(self, now_time):
        create_sql_payment = f"CREATE TABLE If Not Exists `by_payment_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `cost_category` varchar(50) DEFAULT NULL COMMENT '缴费种类', `payment_amount` varchar(50) DEFAULT NULL COMMENT '缴费金额', `pay_date` datetime DEFAULT NULL COMMENT '缴费日期', `name_payer` varchar(50) DEFAULT NULL COMMENT '缴费人', `note_the_code` varchar(50) DEFAULT NULL COMMENT '票据代码', `ticket_number` varchar(50) DEFAULT NULL COMMENT '票据号码', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
        self.execute_sql(create_sql_payment)
        self.bigdata_con.commit()

        create_sql_annual = f"CREATE TABLE If Not Exists `annual_fee_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT, `application_number` varchar(255) DEFAULT NULL, `applicant` varchar(255) DEFAULT NULL COMMENT '专利权人', `money_type` varchar(255) DEFAULT NULL, `money` varchar(255) DEFAULT NULL, `close_date` varchar(255) DEFAULT NULL, `agency` varchar(255) DEFAULT NULL COMMENT '代理机构', `pay_status` varchar(50) DEFAULT '0', `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `upload_inst` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;"
        self.execute_sql(create_sql_annual)
        self.bigdata_con.commit()

        create_sql_overdue = f"CREATE TABLE If Not Exists `overdue_fine_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', `payment_start_time` datetime DEFAULT NULL COMMENT '滞纳金开始时间', `payment_end_time` datetime DEFAULT NULL COMMENT '滞纳金结束时间', `current_annual_fee` varchar(20) DEFAULT NULL COMMENT '当前年费金额', `late_fee` varchar(20) DEFAULT NULL COMMENT '应交滞纳金额', `total` varchar(20) DEFAULT NULL COMMENT '总计', `pay_status` int(5) DEFAULT NULL COMMENT '缴费状态', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL COMMENT '更改时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
        self.execute_sql(create_sql_overdue)
        self.bigdata_con.commit()

    def is_search_ok(self, browser):
        page_source = browser.page_source
        html = etree.HTML(page_source)
        chrc = ''.join(html.xpath('//p/text()'))
        if '费用信息' in chrc:
            print('查询页面登录成功')
        else:
            print('查询页面没登录进去')
            time.sleep(3)
            self.is_search_ok(browser)

    def login_gov(self):
        """ 登录中国及多国 """
        profile = webdriver.FirefoxOptions()
        profile.set_preference('network.proxy.type', 1)
        browser = webdriver.Firefox(options=profile, executable_path=r'D:\工具\geckodriver-v0.15.0-win64\geckodriver.exe')
        browser.get(self.get_url)
        time.sleep(5)
        WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.ID, "username"))).clear()
        browser.find_element_by_id('username').send_keys('CN00675769')
        time.sleep(2)
        browser.find_element_by_id('password').clear()
        browser.find_element_by_id('password').send_keys('Gv89NWWY@')
        self.is_search_ok(browser)
        time.sleep(10)
        current_url = browser.current_url
        return browser, current_url

    def get_application_number(self):
        """ 获取将要抓取的申请号 """
        select_sql = "select application_number from finals where application_number !=''"
        try:
            self.develop_cur.execute(select_sql)
        except:
            print(select_sql)
        all_data = self.develop_cur.fetchall()
        for one_data in all_data:
            self.redis_con.sadd("协和接口查询", one_data[0])

    def execute_sql(self, insert_sql):
        """ 执行sql插入 """
        try:
            self.bigdata_cur.execute(insert_sql)
        except Exception as E:
            print(insert_sql)
            print("插入错误：", E)

    def get_payment_information(self, soup, sqh, now_time):
        """ 获取缴费信息 """
        jf_div_list = soup.select("div[id='djfid'] table tr")  # 未缴费
        jf_list = []
        if len(jf_div_list) > 1:
            for jf_tsg in jf_div_list[1:]:
                jf_item = {}
                jf_td_list = jf_tsg.select("td > span")
                jf_item["cost_category"] = jf_td_list[0]["title"]  # 费用种类
                jf_item["pay_amount"] = jf_td_list[1]["title"]  # 应缴金额
                jf_item["close_date"] = jf_td_list[2]["title"]  # 缴费截止日
                jf_item["pay_status"] = jf_td_list[3]["title"]  # 费用状态
                insert_sql = f"INSERT INTO annual_fee_information_{now_time} (application_number, money_type, money, close_date, pay_status, created_at) " \
                             f"VALUES('{sqh}', '{jf_item['cost_category']}', '{jf_item['pay_amount']}', '{jf_item['close_date']}', '{jf_item['pay_status']}', NOW())"
                self.execute_sql(insert_sql)
                jf_list.append(jf_item)
        return jf_list

    def By_payment_information(self, soup, sqh, now_time):
        """ 获取以缴费信息 """
        yjf_div_list = soup.select(("div[id='yjfid'] table tr"))  # 已缴费
        yjf_list = []
        if len(yjf_div_list) > 1:
            for jf_tsg in yjf_div_list[1:]:
                yjf_item = {}
                jf_td_list = jf_tsg.select("td > span")
                yjf_item["cost_category"] = jf_td_list[0]["title"]  # 费用种类
                yjf_item["payment_amount"] = jf_td_list[1]["title"]  # 缴费金额
                yjf_item["pay_date"] = jf_td_list[2]["title"]  # 缴费日期
                yjf_item["name_payer"] = jf_td_list[3]["title"]  # 缴费人
                yjf_item["note_the_code"] = jf_td_list[4]["title"]  # 票据代码
                yjf_item["ticket_number"] = jf_td_list[5]["title"]  # 票据号码
                yjf_list.append(yjf_item)
                insert_sql = f"INSERT INTO by_payment_information_{now_time} (application_number, cost_category, payment_amount, pay_date, name_payer, note_the_code, ticket_number, create_time, updata_time) " \
                             f"VALUES('{sqh}', '{yjf_item['cost_category']}', '{yjf_item['payment_amount']}', '{yjf_item['pay_date']}', " \
                             f"'{yjf_item['name_payer']}', '{yjf_item['note_the_code']}', '{yjf_item['ticket_number']}', NOW(), NOW())"
                self.execute_sql(insert_sql)
        return yjf_list

    def overdue_fine(self, soup, sqh, now_time):
        """ 滞纳金信息 """
        overdue_div_list = soup.select(("div[id='znjid'] table tr"))  # 滞纳金
        overdue_list = []
        if len(overdue_div_list) > 1:
            for overdue_tsg in overdue_div_list[1:]:
                overdue_item = {}
                overdue_td_list = overdue_tsg.select("td > span")
                payment_time = overdue_td_list[0]["title"].replace("年", "-").replace("月", "-").replace("日", "-").split(
                    "到")  # 缴费时间
                overdue_item["payment_start_time"] = payment_time[0][:-1]
                overdue_item["payment_end_time"] = payment_time[1][:-1]
                overdue_item["current_annual_fee"] = overdue_td_list[1]["title"]  # 当前年费金额
                overdue_item["late_fee"] = overdue_td_list[2]["title"]  # 应交滞纳金额
                overdue_item["total"] = overdue_td_list[3]["title"]  # 总计
                overdue_list.append(overdue_item)
                insert_sql = f"INSERT INTO overdue_fine_{now_time} (application_number, payment_start_time, payment_end_time, current_annual_fee, late_fee, total, create_time, updata_time) " \
                             f"VALUES('{sqh}', '{overdue_item['payment_start_time']}', '{overdue_item['payment_end_time']}', '{overdue_item['current_annual_fee']}', '{overdue_item['late_fee']}', '{overdue_item['total']}', NOW(), NOW())"
                self.execute_sql(insert_sql)
        return overdue_list

    def get_msg(self, sqh, browser, current_url, now_time):
        sqh = re.sub(r'ZL', '', sqh)
        pat = ''.join(re.findall(r'select-key:shenqingh=(.*?)&select-key', current_url))
        url = re.sub(pat, sqh, current_url)
        browser.get(url)
        count_page = browser.page_source
        soup = BeautifulSoup(count_page, "lxml")
        if "应缴费信息" in count_page:
            print('-' * 50, "开始抓取未缴费信息", '-' * 50)
            jf_item = self.get_payment_information(soup, sqh, now_time)  # 获取缴费信息
            print(f"抓取到未缴费信息 {len(jf_item)} 条")
            print('-' * 50, "开始抓取以缴费信息", '-' * 50)
            yjf_item = self.By_payment_information(soup, sqh, now_time)  # 获取以缴费信息
            print(f"抓取到以缴费信息 {len(yjf_item)} 条")
            print('-' * 50, "开始抓取滞纳金信息", '-' * 50)
            overdue_item = self.overdue_fine(soup, sqh, now_time)  # 获取滞纳金信息
            print(f"抓取到滞纳金信息 {len(overdue_item)} 条")
            if len(jf_item) == 0 and len(yjf_item) == 0 and len(overdue_item) == 0:
                self.redis_con.sadd("信合红谷接口查询", sqh)
            elif len(jf_item) == 0 and len(yjf_item) == 0 and len(overdue_item) > 0:
                self.redis_con.sadd("外部接口接口查询", sqh)
                delet_sql = f"DELETE FROM overdue_fine_{now_time} WHERE application_number='{sqh}'"
                self.execute_sql(delet_sql)
        elif '账号登录' in browser.page_source:
            print('\n', "账号被挤或异常掉线，已退出！")
            sys.exit()
        else:
            sqh = f"ZL{sqh}"
            print(f"网站查询上限，并将申请号：{sqh}插入redis库中")
            self.redis_con.sadd("协和端口查询", sqh)
            time.sleep(1000)

    def main_fun(self):
        now_time = time.strftime("%m%d", time.localtime())
        # now_time = "0516"
        self.create_table(now_time)
        browser, current_url = self.login_gov()
        # print(self.redis_con.scard("协和端口查询"))     # 协和端口查询
        need_time = 0
        while self.redis_con.scard("协和端口查询"):
            start_time = time.time()
            application_number = self.redis_con.spop("协和端口查询")

            print('-' * 20, f"开始抓取 {application_number} 的信息, 剩余 {self.redis_con.scard('协和端口查询')} 个, 大约 {need_time} 分钟",
                  '-' * 20)
            self.get_msg(application_number, browser, current_url, now_time)  # 获取专利缴费信息
            self.bigdata_con.commit()
            print()
            print()
            end_time = time.time()
            need_time = round((end_time - start_time) * DB_connect().patents_reids().scard("协和端口查询") / 60, 1)
        self.bigdata_cur.close()
        self.bigdata_con.close()
        self.develop_cur.close()
        self.develop_con.close()
        browser.close()


if __name__ == '__main__':
    annual_msg().main_fun()  # 2020207090033
